﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for Gis
/// </summary>
public class Gis
{
    private SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);

	public Gis()
	{

	}

    public void updateLatLng(int property_id, double lat, double lng)
    {
        SqlDataReader rdr = null;

        SqlTransaction update_transaction = null;

        bool has_property_id = false;
        bool has_lat_lng = false;

        try
        {
            conn.Open();

            string query_string = "SELECT property_id, latitude, longitude FROM property_GIS WHERE property_id = '" + property_id + "'";

            SqlCommand cmd = new SqlCommand(query_string, conn);

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                if (rdr[0] != DBNull.Value)
                {
                    has_property_id = true;
                }
                if (rdr[1] != DBNull.Value && rdr[2] != DBNull.Value)
                {
                    has_lat_lng = true;
                }
            }
            rdr.Close();

            if (has_property_id == false)
            {
                update_transaction = conn.BeginTransaction();

                string insert_property_GIS_string = "INSERT INTO property_GIS (property_id, latitude, longitude) VALUES ('" + property_id + "', " + lat + ", " + lng + ") ";
                SqlCommand insert_property_GIS_cmd = new SqlCommand(insert_property_GIS_string, conn, update_transaction);
                insert_property_GIS_cmd.ExecuteNonQuery();

                update_transaction.Commit();
            }
            else if (has_property_id == true && has_lat_lng == false)
            {
                update_transaction = conn.BeginTransaction();

                string update_property_GIS_string = "UPDATE property_GIS SET latitude = " + lat + ", longitude = " + lng + "WHERE property_id = '" + property_id + "'";
                SqlCommand update_property_GIS_cmd = new SqlCommand(update_property_GIS_string, conn, update_transaction);
                update_property_GIS_cmd.ExecuteNonQuery();

                update_transaction.Commit();         
            }
        }
        catch (SqlException se)
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (update_transaction != null)
            {
                update_transaction.Rollback();
            }
        }
        catch (Exception e)
        {

        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    public string getPrimaryPicture(int property_id)
    {
        string url = "";
        string parcel_id = "";
        int gis_property_id = 0;
    
        SqlDataReader rdr = null;

        try
        {
            conn.Open();

            string query_string = "SELECT p.parcel_id, gis_pic.property_id, gis_pic.url FROM ";
            query_string += "(SELECT property_id, parcel_id FROM property WHERE property_id = " + property_id + " ) p LEFT JOIN ";
            query_string += "(SELECT gis.property_id, primary_picture_id, url FROM property_GIS gis LEFT JOIN ";
            query_string += "picture pic ON gis.property_id = " + property_id + " and primary_picture_id = picture_id and gis.property_id = pic.property_id) gis_pic ";
            query_string += "ON p.property_id = gis_pic.property_id ";

            SqlCommand cmd = new SqlCommand (query_string, conn);

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                if (rdr[0] != DBNull.Value)
                {
                    parcel_id = (string)rdr[0];
                }
                if (rdr[1] != DBNull.Value)
                {
                    gis_property_id = (int)rdr[1];
                }
                if (rdr[2] != DBNull.Value)
                {
                    url = (string)rdr[2];
                }
            }

            rdr.Close();

            if (url == "")
            {
                WebClient client = new WebClient();
                string start = "http://franklincountyoh.metacama.com/do/selectDisplay?parcelid=";
                string end = "&select=PHOTO&curpage=*";

                SqlTransaction update_transaction = conn.BeginTransaction();

                try
                {
                    string source = string.Concat(start, parcel_id, end);
                    string s = client.DownloadString(source);

                    //Find the specific image location - something like "/photo/20080121/FL101354.jpg"
                    int first = s.IndexOf("/photo/");
                    string x = s.Remove(0, first - 1);
                    string[] Split = x.Split(new Char[] { '"' });

                    //Put the two parts together to get the direct URL for the image
                    string y = "http://franklincountyoh.metacama.com"; //part 1
                    url = string.Concat(y, Convert.ToString(Split[1])); //part 2

                    //insert new picture into picture and property_GIS
                    if (gis_property_id == 0)
                    {
                        int picture_id = 0;

                        SqlCommand get_picture_id_cmd = new SqlCommand("SELECT MAX(picture_id) FROM picture", conn, update_transaction);
                        rdr = get_picture_id_cmd.ExecuteReader();

                        if (rdr.Read())
                        {
                            if (rdr[0] != DBNull.Value)
                            {
                                picture_id = (int)rdr[0];  
                            }
                        }
           
                        rdr.Close();

                        picture_id++;

                        string insert_picture_string = "INSERT INTO picture (picture_id, property_id, url) VALUES ('" + picture_id +"', '" + property_id + "', '" + url + "') ";
                        SqlCommand insert_picture_cmd = new SqlCommand(insert_picture_string, conn, update_transaction);
                        insert_picture_cmd.ExecuteNonQuery();

                        string insert_property_GIS_string = "INSERT INTO property_GIS (property_id, primary_picture_id) VALUES ('" + property_id + "', '" + picture_id + "') ";
                        SqlCommand insert_property_GIS_cmd = new SqlCommand(insert_property_GIS_string, conn, update_transaction);
                        insert_property_GIS_cmd.ExecuteNonQuery();
           
                    }
                    //insert new picture into picture and update property_GIS
                    else 
                    {
                        int picture_id = 0;

                        SqlCommand get_picture_id_cmd = new SqlCommand("SELECT MAX(picture_id) FROM picture", conn, update_transaction);
                        rdr = get_picture_id_cmd.ExecuteReader();

                        if (rdr.Read())
                        {
                            if (rdr[0] != DBNull.Value)
                            {
                                picture_id = (int)rdr[0];
                            }
                        }
                        rdr.Close();

                        picture_id++;

                        string insert_picture_string = "INSERT INTO picture (picture_id, property_id, url) VALUES ('" + picture_id + "', '" + property_id + "', '" + url + "') ";
                        SqlCommand insert_picture_cmd = new SqlCommand(insert_picture_string, conn, update_transaction);
                        insert_picture_cmd.ExecuteNonQuery();
                        string update_property_GIS_string = "UPDATE property_GIS SET primary_picture_id = '" + picture_id + "' WHERE property_id = '" + property_id + "'";
                        SqlCommand update_property_GIS_cmd = new SqlCommand(update_property_GIS_string, conn, update_transaction);
                        update_property_GIS_cmd.ExecuteNonQuery();
                    }

                    update_transaction.Commit();
                }
                catch(SqlException se)
                {
                    Console.Write(se.Message);
                    if (rdr != null)
                    {
                        rdr.Close();
                    }
                    if(update_transaction != null)
                    {
                        update_transaction.Rollback();
                    }
                }
                catch(Exception e)
                {
             
                }
            }
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
        
        return url;
    }

    public  string getParcelID(int property_id)
    {
 
        SqlDataReader rdr = null;

        string parcel_id = "-1";
        try
        {
            conn.Open();

            //SqlCommand cmd = new SqlCommand("EXECUTE getParcelID " + property_id, conn);
            SqlCommand cmd = new SqlCommand("SELECT parcel_id FROM property WHERE property_id='" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                parcel_id = (string)rdr[0];
            }
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return parcel_id;
    }

    public  string getAddress(int property_id)
    {
        string address = "";
       
        SqlDataReader rdr = null;

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT street_address FROM property WHERE property_id='" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            if(rdr.Read())
            {
                address = (string)rdr[0];
                //address += rdr[0] + ", " + rdr[1] + ",";
                /*
                if (rdr[2] != DBNull.Value)
                {
                    address += rdr[2];
                }*/
            }
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
        return address + ", Franklin, OH";
    }

    public  bool hasTrendsTeamDistance(int property_id)
    {
        SqlDataReader rdr = null;

        bool has_data = false;
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM trends_team_distance WHERE property_id = '" + property_id + "'", conn);
            
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                has_data = true;
            }
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }

        return has_data;
    }

    public double[] getLatLng(int property_id)
    {
        double[] latlng = new double[]{0, 0};

        SqlDataReader rdr = null;

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT latitude, longitude FROM property_GIS WHERE property_id='" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                if (rdr[0] != DBNull.Value && rdr[1] != DBNull.Value)
                {
                    latlng[0] = (double)rdr[0];
                    latlng[1] = (double)rdr[1];
                }
            }
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
        return latlng;
    }

    public double getStraightLineDistance(double lat2, double lat1, double lon2, double lon1)
    {

        int R = 6371; // km

        double dLat = Math.PI * (lat2 - lat1) / 180.0;
        double dLon = Math.PI * (lon2 - lon1) / 180.0;
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
            Math.Cos(Math.PI * lat1 / 180.0) * Math.Cos(Math.PI * lat2 / 180.0) *
            Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
        double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
        double d = R * c;
        double distance = d / 1.609344;
        return distance;
    }

    public double getMinCityCenterDistance(double lat1, double lon1)
    {
        double[] distance = new double[20];
        double[] arrayLat = new double[]{39.973699861154266, 39.95750073989836, 39.87763319972297
            , 39.961875703238874, 39.95383279178168, 40.01522442824767, 40.0099982591023,3,
            40.02656285299746, 39.95968825655743};
        double[] arrayLon = new double[]{-83.00108671188354, -83.00331830978393, -83.0926251411438,
            -83.04516077041626, -82.98192501068115, -82.80474901199341, -82.8643798828125, -82.95364379882812,
            -83.0552887916565, -82.96772003173828};
        for (int i = 0; i < arrayLat.Length; i++)
        {
            for (int j = 0; j < arrayLon.Length; j++)
            {
                distance = new double[] { getStraightLineDistance(arrayLat[i], lat1, arrayLon[j], lon1) };
            }
        }

        //sorting the array in increasing order
        Array.Sort<double>(distance);
        return distance[0];
    }

    public  void updateTrendsTeamDistance(int property_id, string[] columns, double[] distance)
    {

        double[] property_latlng = getLatLng(property_id);
        double minCityCenterDistance = getMinCityCenterDistance(property_latlng[0], property_latlng[1]);

        SqlDataReader rdr = null;

        bool has_data = false;
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM trends_team_distance WHERE property_id = '" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                has_data = true;
            }

            rdr.Close();

            int i = 0;
            string cmd_string = "";
            if (has_data)
            {
                cmd_string = "UPDATE trends_team_distance SET ";
                foreach (double dist in distance)
                {
                    if (i > 0)
                    {
                        cmd_string += ", ";
                    }
                    cmd_string += columns[i] + "=" + dist;
                    i++;
                }
                cmd_string += ", dist_to_city_center = " + minCityCenterDistance + " ";
                cmd_string += "WHERE property_id = '" + property_id + "'";
            }
            else
            {
                cmd_string = "INSERT INTO trends_team_distance ";
                string col_string = "(property_id, ";
                string val_string = "VALUES ('" + property_id + "', ";
                foreach (double dist in distance)
                {
                    if (i > 0)
                    {
                        col_string += ", ";
                        val_string += ", ";
                    }
                    col_string += columns[i];
                    val_string += dist;
                    i++;
                }
                col_string += ", dist_to_city_center) ";
                val_string += ", "+ minCityCenterDistance +") ";
                cmd_string += col_string + val_string;
            }
            SqlCommand upd_cmd = new SqlCommand(cmd_string, conn);
            upd_cmd.ExecuteNonQuery();
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (conn != null)
            {
                conn.Close();
            }
        }
    }         
}
